package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.SaleBranch;
import com.turing.post.organization.util.SaleBranchDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Dec 18, 2012
 */
@Stateless(mappedName = "organization.impl.SaleBranchDaoImpl")
@Remote
@Local
public class SaleBranchDaoImpl implements SaleBranchDao {
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public SaleBranch getSaleBranchBySaleBranchName(String saleBranchName) {
		String jpql = "select * from SaleBranch c where c.SaleBranchName = ?1";
		Query query = _em.createNativeQuery(jpql, SaleBranch.class);
		query.setParameter(1, saleBranchName);
		List<SaleBranch> list = query.getResultList();
		if (list == null || list.isEmpty() || list.size() < 1) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public SaleBranch getSaleBranchBySaleBranchNo(String saleBranchNo) {
		return _em.find(SaleBranch.class, saleBranchNo);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleBranch> getSaleBranchList(PCPSWebInput webInput,
			String saleBranchNo, String saleBranchName, String regionNo,
			Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "select * from SaleBranch t where 1=1";
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
			sql = sql + " and t.SaleBranchNo like '" + issueCompanyNo + "%'";
		}
		if (!PCPSUtil.isEmpty(saleBranchNo)) {
			sql = sql + " and t.SaleBranchNo like '%" + saleBranchNo + "%'";
		}
		if (!PCPSUtil.isEmpty(saleBranchName)) {
			sql = sql + " and t.SaleBranchName like '%" + saleBranchName + "%'";
		}
		if (!PCPSUtil.isEmpty(regionNo)) {
			sql = sql + " and t.RegionNo like '" + regionNo + "%'";
		}
		sql = sql + " order by saleBranchNo";
		Query query = _em.createNativeQuery(sql, SaleBranch.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	@Override
	public BigInteger getSaleBranchTotal(PCPSWebInput webInput,
			String saleBranchNo, String saleBranchName, String regionNo) {
		String sql = "select nvl(count(*),0) from SaleBranch t where 1=1";
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
			sql = sql + " and t.SaleBranchNo like '" + issueCompanyNo + "%'";
		}
		if (!PCPSUtil.isEmpty(saleBranchNo)) {
			sql = sql + " and t.SaleBranchNo like '%" + saleBranchNo + "%'";
		}
		if (!PCPSUtil.isEmpty(saleBranchName)) {
			sql = sql + " and t.SaleBranchName like '%" + saleBranchName + "%'";
		}
		if (!PCPSUtil.isEmpty(regionNo)) {
			sql = sql + " and t.RegionNo like '" + regionNo + "%'";
		}
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void insertSaleBranch(SaleBranch saleBranch)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, saleBranch);
	}

	@Override
	public void updateSaleBranch(SaleBranch saleBranch)
			throws PCPSApplicationException {
		GeneralDao.update(_em, saleBranch);
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<OrganTreeList> getSaleBranchListBySaleBranchNo(String
	// branchNo,
	// Integer minLen) {
	// String jpql = "select sb.salebranchno branchNo,sb.salebranchname
	// branchName,nvl(sb.supersalebranchno,'0') superBranchNo,"
	// + "case when (length(sb.salebranchno)-"
	// + minLen
	// + ")=0 then 1 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=2 then 2 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=4 then 3 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=6 then 4 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=10 then 6 end as layer from SaleBranch ib where sb.salebranchno like
	// '"
	// + branchNo + "%' order by sb.salebranchno";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(branchNo)) {
	// jpql = "select sb.salebranchno branchNo,sb.salebranchname
	// branchName,nvl(sb.supersalebranchno,'0') superBranchNo,"
	// + "case when (length(sb.salebranchno)-"
	// + minLen
	// + ")=0 then 1 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=2 then 2 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=4 then 3 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=6 then 4 when (length(sb.salebranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(sb.salebranchno)-"
	// + minLen
	// + ")=10 then 6 end as layer from SaleBranch ib order by sb.salebranchno";
	// }
	// Query query = _em.createNativeQuery(jpql, OrganTreeList.class);
	// List<OrganTreeList> list = query.getResultList();
	// return list;
	// }
	//
	// @Override
	// public Integer getSaleBranchMinLenSalebBanchNo(String branchNo) {
	// String sql = "select nvl(min(length(sb.salebranchno)),0) minlength from
	// salebranch sb where sb.salebranchno like '"
	// + branchNo + "%'";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(branchNo)) {
	// sql = "select nvl(min(length(sb.salebranchno)),0) minlength from
	// salebranch sb";
	// }
	// Query query = _em.createNativeQuery(sql);
	// return ((BigDecimal) query.getSingleResult()).intValue();
	// }

	@SuppressWarnings("unchecked")
	public SaleBranch getSaleBranchForUpdate(String saleBranchNo) {
		String sql = "select * from SaleBranch t where t.salebranchno=?1 for update";
		Query query = _em.createNativeQuery(sql, SaleBranch.class);
		query.setParameter(1, saleBranchNo);
		List<SaleBranch> aclist = query.getResultList();
		if (aclist == null || aclist.size() != 1)
			return null;
		SaleBranch ac = aclist.get(0);
		_em.refresh(ac);
		return getSaleBranchBySaleBranchNo(ac.getSaleBranchNo());
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<SaleBranch> getSaleBranchListBySaleCompanyNoIsNotNull(
	// String saleCompanyNo) {
	// String sql =
	// "select * from SaleBranch t where t.salecompanyno is not null and
	// substr(t.salebranchno,0,6)='"
	// + saleCompanyNo + "' order by t.salebranchno";
	// Query query = _em.createNativeQuery(sql, SaleBranch.class);
	// return query.getResultList();
	// }

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleBranch> getSalesSaleBranchList(PCPSWebInput webInput) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select distinct(t.salebranchno),s.salebranchname from sales t,salebranch s where t.salebranchno=s.salebranchno and  substr(t.salebranchno,0,6)='"
				+ issueCompanyNo + "'";
		Query query = _em.createNativeQuery(sql, SaleBranch.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleBranch> getAllSaleBranchByIssueCompanyNo(
			String issueCompanyNo) {
		String sql = "select t.* from salebranch t where substr(t.salebranchno,0,6) = ?1";
		Query query = _em.createNativeQuery(sql, SaleBranch.class);
		query.setParameter(1, issueCompanyNo);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleBranch> getSerchSaleBranchCashListByCondition(
			PCPSWebInput webInput, String searchType, String saleBranchNo,
			BigInteger startMoney, BigInteger endMoney, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "";
		// OverCash 现金库超限查询 Cash 现金库查询 Credit预存款查询
		if ("OverCash".equals(searchType)) {
			sql = "select sb.* from salebranch sb,(select IssueCompanyNo,CashUpperLimit from issuecompany where CashUpperLimit>0)i "
					+ "where substr(sb.salebranchno,0,6)=i.IssueCompanyNo and sb.cash>i.CashUpperLimit "
					+ "and (?1 is null or Cash>=to_number(?1)) and (?2 is null or Cash<=to_number(?2))";
		} else if ("Cash".equals(searchType)) {
			sql = "select sb.* from salebranch sb where 1=1 "
					+ "and (?1 is null or Cash>=to_number(?1)) and (?2 is null or Cash<=to_number(?2))";
		} else if ("Credit".equals(searchType)) {
			sql = "select sb.* from salebranch sb where 1=1 "
					+ "and (?1 is null or CreditLowLimit>=to_number(?1)) and (?2 is null or CreditLowLimit<=to_number(?2))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		sql += " order by SaleBranchNo";
		// PCPSUtil.Log("=========="+sql);
		Query query = _em.createNativeQuery(sql, SaleBranch.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, startMoney);
		query.setParameter(2, endMoney);
		return query.getResultList();
	}

	@Override
	public BigInteger getSerchSaleBranchCashTotalByCondition(
			PCPSWebInput webInput, String searchType, String saleBranchNo,
			BigInteger startMoney, BigInteger endMoney) {
		String sql = "";
		// OverCash 现金库超限查询 Cash 现金库查询 Credit预存款查询
		if ("OverCash".equals(searchType)) {
			sql = "select nvl(count(*),0) from salebranch sb,(select IssueCompanyNo,CashUpperLimit from issuecompany where CashUpperLimit>0)i "
					+ "where substr(sb.salebranchno,0,6)=i.IssueCompanyNo and sb.cash>i.CashUpperLimit "
					+ "and (?1 is null or Cash>=to_number(?1)) and (?2 is null or Cash<=to_number(?2))";
		} else if ("Cash".equals(searchType)) {
			sql = "select nvl(count(*),0) from salebranch sb where 1=1 "
					+ "and (?1 is null or Cash>=to_number(?1)) and (?2 is null or Cash<=to_number(?2))";
		} else if ("Credit".equals(searchType)) {
			sql = "select nvl(count(*),0) from salebranch sb where 1=1 "
					+ "and (?1 is null or CreditLowLimit>=to_number(?1)) and (?2 is null or CreditLowLimit<=to_number(?2))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// PCPSUtil.Log("=========="+sql);
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startMoney);
		query.setParameter(2, endMoney);
		return new BigInteger(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getSaleBranchReport(PCPSWebInput webInput) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select s.salebranchno salebranchno,s.salebranchname salebranchname,s.supersalebranchno supersalebranchno,ss.salebranchname supersalebranchname,s.regionno regionno,'销售机构' branchtype,s.linkman linkman,s.linktele linktele,s.address address "
				+ "from salebranch s left join salebranch ss on s.supersalebranchno = ss.salebranchno where (?1 is null or substr(s.salebranchno,0,6)=?1) order by s.salebranchno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		List<Object[]> list = query.getResultList();
		return list;
	}

	// 获取销售机构自动生成顺序号
	@Override
	public String getAutoSaleBranchSeqNo(String saleBranchNo, String regionNo) {
		if (PCPSUtil.isEmpty(saleBranchNo)) {
			return "";
		}
		String sql = "";
		// 发卡机构下销售机构顺序号
		if (PCPSUtil.isEmpty(regionNo)) {
			sql = "select nvl(max(substr(t.salebranchno,14,3)),0)+1 seqno from salebranch t where t.issuebranchno is not null and t.supersalebranchno is null and (substr(t.salebranchno,0,13))='"
					+ saleBranchNo + "'";
			Query query = _em.createNativeQuery(sql);
			return String.format("%03d", Integer.parseInt(query
					.getSingleResult().toString()));
		} else {
			// 销售机构层级顺序号
			Integer length = saleBranchNo.trim().length();
			Integer length1 = saleBranchNo.trim().length() + 1;
			Integer length2 = saleBranchNo.trim().length() + 2;
			sql = "select nvl(max(substr(t.salebranchno,"
					+ length1
					+ ",2)),0)+1 seq from salebranch t where length(t.salebranchno)="
					+ length2 + " and substr(t.salebranchno,0," + length
					+ ")='" + saleBranchNo + "'";
			Query query = _em.createNativeQuery(sql);
			return String.format("%02d", Integer.parseInt(query
					.getSingleResult().toString()));
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public Map<String, String> getSaleBrchMapByIssueCompanyNo(
			String issueCompanyNo) {
		Map<String, String> map = new HashMap<String, String>();
		String sql = "select t.issuebranchno,s.salebranchno from issuebranch t,salebranch s"
				+ " where t.issuebranchno like ?1"
				+ " and t.issuebranchno= s.issuebranchno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo + "%");
		List<Object[]> list = query.getResultList();
		if (list == null || list.size() == 0) {
			return null;
		}
		for (int i = 0; i < list.size(); i++) {
			String issuebranchno = list.get(i)[0].toString();
			String salebranchnos = list.get(i)[1].toString();
			if (PCPSUtil.isEmpty(map.get(issuebranchno))) {
				map.put(issuebranchno, salebranchnos);
			} else {
				map.put(issuebranchno, map.get(issuebranchno) + ","
						+ salebranchnos);
			}
		}
		return map;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleBranch> getSaleBrchByIssueBrchNo(String issueBrchNo) {
		String sql = "select s.* from issuebranch t,salebranch s"
				+ " where t.issuebranchno = ?1"
				+ " and t.issuebranchno= s.issuebranchno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueBrchNo + "%");
		List<SaleBranch> list = query.getResultList();
		if (list == null || list.size() == 0) {
			return null;
		}
		return list;
	}

	@Override
	public void deleteSaleBranch(String branchNo)
			throws PCPSApplicationException {
		try {
			String sql2 = "delete from SaleBranch where SaleBranchNo='"
					+ branchNo + "'";
			Query query2 = _em.createNativeQuery(sql2);
			query2.executeUpdate();
		} catch (Exception e) {
			throw new PCPSApplicationException(
					PCPSApplicationException.ERRCODE_CATEGORY_DATABASE,
					"delete", "该机构已被引用，禁止删除!", false);
		}
	}

	/**
	 * 设置指定机构日终标志
	 * 
	 * @param issueCompanyNo
	 *            发卡商
	 * @param saleBranchNo
	 *            (可选) 销售机构号
	 * @param status
	 *            日终标志
	 */
	@TransactionAttribute(javax.ejb.TransactionAttributeType.REQUIRES_NEW)
	public void setDayOffStatus(String issueCompanyNo, String saleBranchNo,
			String status) {
		String sql = "update SaleBranch set DayOff = ?3 where SaleBranchNo like ?1 and (SaleBranchNo like ?2 OR ?2 IS NULL)";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo + "%");
		if (PCPSUtil.isEmpty(saleBranchNo))
			query.setParameter(2, null);
		else
			query.setParameter(2, saleBranchNo);
		query.setParameter(3, status);
		query.executeUpdate();
	}

}
